<?php
/**
 * 关于用户的对数据库操作模型
 */
class User_model extends Model
{
    /**
     * 用户模型构造函数
     */
    function User_model()
    {
        parent::Model();
    }

    /**
     *获取学校的字母缩写
     */
    function get_schoolinitial(){
        if($this->session->userdata('schoolinitials')!=null){
            $initial[0]=1; //表明session中已经设置了学校
            $initial[1]=$this->session->userdata('schoolinitials');
            return $initial;
        }
        else{
            $initial[0]=0; //表示session中并没有设置学校
            $initial[1]="zju";
            return $initial;
        }
    }

   /**
    *获取学校的数据库编号
    */
   function get_schoolid(){
        if($this->session->userdata('school_id')!=null)
            return $this->session->userdata('school_id');
        else
            return 1;  //默认zju
    }

    /**
    *获取学校中文名称
    */
    function get_schoolname($id){
        $sql="SELECT name from school WHERE id=" .$id;
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return "";   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            return $result[0]->name;
        }
    }

    /**
    *返回用户的默认注册学校的首字母缩写
    */
    function get_school($userid){
          $sql="SELECT * from user WHERE id='$userid'";
          $query=$this->db->query($sql);
          $result=$query->result();
          return $result[0]->school_initials ;
    }


    /**
     * “逛商店”功能的数据库执行函数，可以根据校园返回商店
     */
    function get_shops($total,$school,$campusid,$offset,$perpage){
        if($campusid==-1||$campusid==0){
          if($total==1)
            $sql="SELECT count(id) as number FROM user WHERE school_initials='$school'";
          else
            $sql="SELECT * FROM user WHERE school_initials='$school' ORDER BY numsales+numwanted+numsaledeals+numwanteddeals DESC LIMIT $offset, $perpage";
        }
        else{
          if($total==1)
            $sql="SELECT count(id) as number FROM user WHERE school_initials='$school' AND campus_id=$campusid";
          else
            $sql="SELECT * FROM user WHERE school_initials='$school' AND campus_id=$campusid
                    ORDER BY numsales+numwanted+numsaledeals+numwanteddeals DESC LIMIT $offset, $perpage";
        }
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return 0;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
           $result=$query->result();
           return $result;
        }
    }

     /**
      *进入商店
      */
     function enterShop($userid,$school){
        $saletable="zsaleposts_".$school;
        $wantedtable="zwantedposts_".$school;
        $sql="SELECT * FROM $saletable WHERE user_id='$userid' AND dealed=0";
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            $data['saleposts']=null;
        }
        else{
           $result=$query->result();
           $data['saleposts']=$result;
        }
        $sql2="SELECT * FROM $wantedtable WHERE user_id='$userid' AND dealed=0";
        $query2=$this->db->query($sql2);
        if( ($query2->row_array())==null ){
            $data['wantedposts']=null;
        }
        else{
           $result2=$query2->result();
           $data['wantedposts']=$result2;
        }
        return $data;
    }

    /**
     *获取省份
     */
    function get_province($id){
        $sql = "SELECT * FROM province WHERE pid = " .$id;
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return NULL;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            return $result[0];
        }
    }

    /**
     *获取城市
     */
    function get_city($id){
        $sql = "SELECT * FROM city WHERE cid = " .$id;
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return NULL;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            return $result[0];
        }
    }

    /**
     *获取一个省下面所有城市
     */
    function get_citys($id){
        $sql = "SELECT * FROM city WHERE pid = " .$id. " ORDER BY cid";
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return "";   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            $citys = "";
            foreach($result as $city){
                $citys = $citys . $city->cid . " " . $city->cname . ",";
            }
            return $citys;
        }
    }

    /**
     *获取分类
     */
    function get_categorys($id){
        $sql = "SELECT * FROM `category-2` WHERE parent_id = " .$id. " ORDER BY id";
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return "";   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            $categorys = "";
            foreach($result as $category){
                $categorys = $categorys . $category->id . " " . $category->name . ",";
            }
            return $categorys;
        }
    }

    /**
     *获取学校
     */
    function get_schools($id){
        $sql = "SELECT id,name FROM school WHERE city = " .$id. " ORDER BY id";
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return "";   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            $schools = "";
            foreach($result as $school){
                $schools = $schools . $school->id . " " . $school->name . ",";
            }
            return $schools;
        }
    }

    /**
     *根据校园编号获取校园
     */
    function get_campus($id){
        $sql = "SELECT * FROM campus WHERE id = " .$id;
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return "主校区";   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            return $result[0]->name;
        }
    }

    /**
     *获取一个学校下所有校园
     */
    function get_campuss($school_id){ //school_id
        $sql = "SELECT id,name FROM campus WHERE school_id = " .$school_id. " ORDER BY id";
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return "0 主校区,";   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            $campuss = "";
            foreach($result as $campus){
                $campuss = $campuss . $campus->id . " " . $campus->name . ",";
            }
            return $campuss;
        }
    }

    function get_campus_ofschool($school_id){ //school_id
        $sql = "SELECT id,name FROM campus WHERE school_id = " .$school_id. " ORDER BY id";
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return 0;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            return $result;
        }
    }

    /**
     * 获取有多少未读消息
     */
    function num_unread_msg($username){
        $this->db->where('id', $username);
        $query=$this->db->get('user');
        if( ($query->row_array())==null ){
            return -1;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
           $result=$query->row_array();         
           return $result['unread_message'];   //如果数据库中有符合此用户名的记录则返回1
        }
    }

    /**
     * 获取当前用户接受的所有短消息标题
     */
    function get_msg_theme($kind,$total,$username,$offset,$perpage){
        $name = "receiver";
        if( $kind == 1 )
            $name = "sender";
        if($total==1)
           $sql="SELECT count(*) as number FROM message WHERE " .$name. "_id='".$username."' AND " .$name. "_del=0";
        else
           $sql="SELECT id,theme,time,readOr_not,sender_id,receiver_id FROM message WHERE ".$name."_id='".$username."' AND ".$name."_del=0 order by readOr_not,time DESC LIMIT $offset,$perpage";
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return 0;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
           $result=$query->result();
           return $result;
        }
    }

   /**
    * 获得某个用户接受的短消息内容
    */
   function get_msg_content($kind,$username,$id){
       if( $kind == 'rec' ){
            $sql="SELECT * FROM message WHERE id=".$id. " AND receiver_id='".$username."' AND receiver_del=0";
       }
       else if( $kind == 'send' ){
            $sql="SELECT * FROM message WHERE id=".$id. " AND sender_id='".$username."' AND sender_del=0";
       }
       else{
           return NULL;
       }
        $query=$this->db->query($sql);
        if( ($query->row_array())==null ){
            return NULL;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            if($result[0]->readOr_not==0){ //如果未读
                $unread_msg=$this->num_unread_msg($username);
                $unread_msg--;
                $this->session->set_userdata('unread_msg', $unread_msg);//把未读短消息个数写入session
                $result=$query->result();
                $sql2="UPDATE user SET unread_message=".$unread_msg." WHERE id='".$username."'";
                $this->db->query($sql2);
                $sql3="UPDATE message SET readOr_not=1 WHERE id=".$id;
                $this->db->query($sql3);
            }
            return $result[0];
        }
    }

    /**
     *执行发送短消息的操作
     */
    function send_msg($sender,$receiver,$theme,  $content){
        $sql = "insert into message values('', '', '".$sender."', '".$receiver."', '".$theme."', '".$content."','" .date("Y-m-d H:i:m"). "',0,0,0)";
        $this->db->query($sql);
        $unread_msg = $this->user_model->num_unread_msg($receiver);
        if( $unread_msg == -1 ){
            return "no_receiver";
        }
        else{
            $unread_msg++;
            $this->update_unread_msg($receiver,$unread_msg);
            if( $sender == $receiver ){ //自己发送给自己的情况也要跟新未读短消息个数
                $this->session->set_userdata('unread_msg', $unread_msg);
            }
            return "suc！";
        }
    }

    /**
     *更新用户表中未读消息的条数
     */
    function update_unread_msg($user_id, $unread_msg){
        $sql = "update user set unread_message = " .$unread_msg. " where id = '" .$user_id. "'";
        $this->db->query($sql);
    }

    /**
     *执行数据库中删除短消息的任务
     */
    function delete_msg($kind,$user_id,$msg_id,$read){
        if( $kind == 0 ){
            $sql = "UPDATE message SET sender_del = 1 WHERE id=".$msg_id;
        }
        else{
            $sql = "UPDATE message SET receiver_del = 1 WHERE id=".$msg_id;
        }
        $this->db->query($sql);
        if(($this->db->affected_rows())!=1){
            return 0;      //如果删除操作影响的行数不为1，则表示删除失败，返回0
        }
        else{
            if( $read == 0 ){ //如果未读，将该用户未读信息减一
                $unread_msg = $this->user_model->num_unread_msg($user_id);
                $unread_msg--;
                $this->update_unread_msg($user_id, $unread_msg);
            }
            return 1;    //如果删除成功，返回1
        }
    }

    /**
     * 判断用户名是否已被注册
     */
    function if_registered($get_username){
        $this->db->where('id', $get_username);
        $query=$this->db->get('user');
        if( ($query->row_array())==null ){
            return 0;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            return 1;   //如果数据库中有符合此用户名的记录则返回1
        }
    }

    /**
     *添加新学校
     */
    function add_new_school($data){
        $school = array(
            'report_resean' => "无该校信息",
            'addby_userid' => $data['username'],
            'adduser_email' => $data['email'],
            'province' => $data['province'],
            'city' => $data['city'],
            'type' => $data['school_type'],
            'name' => $data['name'],
            'initials' => $data['initials'],
            'website'  => $data['website']
            );
        $this->db->insert('school_bugreport', $school);
        $sql = "SELECT id FROM school_bugreport WHERE addby_userid = '" .$data['username']. "' AND adduser_email='" .$data['email']. "' AND " .
               "province=" .$data['province']. " AND city=" .$data['city']. " AND type=" .$data['school_type']. " AND name='" .$data['name'] ."'";
        $query = $this->db->query($sql);
        if( ($query->row_array())==null ){
            echo "here";
            return;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->result();
            $school_bugreport_id = $result[0]->id;
            $campus = $data['campus'];
            foreach( $campus as $campus_name){
                if( trim($campus_name) != "" ){
                    $sql2 = "INSERT INTO campus_bugreport (school_bugreport_id,name,location) VALUES(" .$school_bugreport_id. ",'".$campus_name."',"."'')";
                    $this->db->query($sql2);
                }
                //$this->db->insert('campus_bugreport', $campus_arr);
            }
            
        }
    }

    /**
     *选择学校
     */
    function choose_school($id){
        if( $id == NULL || $id == ""){ //采取默认
            $this->session->set_userdata("schoolname","浙江大学");
            $this->session->set_userdata("schoolinitials","zju");
        }
        else{
            $this->db->where('id', $id);
            $query=$this->db->get('school');
            if( ($query->row_array())==null ){
                $this->session->set_userdata("schoolname","浙江大学");
                $this->session->set_userdata("schoolinitials","zju");
            }
            else{
                $result=$query->result();
                $this->session->set_userdata("schoolname",$result[0]->name);
                $this->session->set_userdata("schoolinitials",$result[0]->initials);
            }
        }
    }

    /**
     *在数据库中添加新用户
     */
    function add_new_user($userdata){
        $id=$userdata['username'];
        $password=MD5($userdata['password']); //密码进行加密处理
        $email=$userdata['email'];
        $shopname=$userdata['shopname'];
        $name=$userdata['realname'];
        $phone=$userdata['phone'];
        $short_phone=$userdata['short_phone'];
        $dorm=$userdata['dorm'];
        $qq=$userdata['qq'];
        $role=$userdata['role'];
        $province = $userdata['province'];
        $city = $userdata['city'];
        $school = $userdata['school'];
        $campus = $userdata['campus'];
        $initials = $userdata['school_initials'];

        $money=50;
        $data = array(
               'id' => $id ,
               'password' => $password ,
               'email' => $email,
               'shopname'=>$shopname,
               'name'=>$name,
               'phone' => $phone,
               'short_phone' => $short_phone,
               'dorm'=>$dorm,
               'qq' => $qq,
               'role' => $role,
               'money'=>$money,
               'province_id'=>$province,
               'city_id'=>$city,
               'school_id'=>$school,
               'school_initials'=>$initials,
               'campus_id'=>$campus
        );
        $this->db->insert('user', $data);    //插入数据

        if(($this->db->affected_rows())!=1){
            return 0;      //如果插入操作影响的行数不为1，则表示插入失败，返回0
        }
        else{
            return 1;    //如果插入成功，返回1
        }
    }


    /**
     * 删除用户  这里需要把传入数据改为字符串！
     */
    function delete_user($userid){
        $delete_string="DELETE FROM users WHERE id={$userid}";
        $this->db->query($delete_string);
        if(($this->db->affected_rows())!=1){
            return 0;      //如果删除操作影响的行数不为1，则表示删除失败，返回0
        }
        else{
            return 1;    //如果删除成功，返回1
        }
    }


    /**
     * 用户登录验证函数
     */
    function user_login_validate($input_username,$input_password){
        $this->db->where('id', $input_username);
        $query=$this->db->get('user');
        if( ($query->row_array())==null ){
            $this->session->set_userdata('school_id', "");//设置学校的id为空，默认学校为zju
            return 0;   //如果数据库中没有符合此用户名的记录，则返回-1
        }
        else{
            $result=$query->row_array();
            if((MD5($input_password))==$result['password']){
                $this->session->set_userdata('school_id', $result['school_id']);//设置学校的id
                $this->session->set_userdata('email', $result['email']);//设置学校的id
                return 1;   //如果密码正确则返回1
            }
            else{
                return 0;   //如果密码错误则返回0
            }
        }
    }

    //后台，员工登录验证函数
    function staff_login_validate($id,$input_password){
        $this->db->where('id', $id);
        $query=$this->db->get('staff');
        if( ($query->row_array())==null ){
            return -1;   //如果数据库中没有符合此用户名的记录，则返回0
        }
        else{
            $result=$query->row_array();
            if(md5($input_password)==$result['password']){
                return $result['role'];   //如果密码正确则返回员工类型
            }
            else{
                return 0;   //如果密码错误则返回0
            }
        }
    }

    /**
     * 判断用户是否登录并编码网页右上方“登录|注册”等相应的链接
     */
    function get_user_status($continue="yes"){
        $this->load->library('session');
        $this->load->helper('cookie');
        $siteurl=site_url();
        //$currentPage=$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF'].'?'.$_SERVER['QUERY_STRING'];
        $currentPage=$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'];
        /*if(isset($_COOKIE['username'])){
            $this->session->set_userdata('username', $_COOKIE['username']); 
            $currentPage="user/logout?continue=".$continue."&currentpage=".htmlentities(urlencode($currentPage));
            $unread_msg=$this->num_unread_msg($this->session->userdata('username'));
            $this->session->set_userdata('unread_msg', $unread_msg);          
            $link= "<font color='blue'><b>{$this->session->userdata('username')}</b></font>&nbsp;<a href='{$siteurl}/user/read_msg_theme'>$unread_msg 未读</a>&nbsp;<a href='{$siteurl}/user/my_salepost'>我的书店</a>&nbsp;&nbsp;".anchor($currentPage, '退出');
            return $link; 
        }
        else*/if(true){
           if($this->session->userdata('username')==""){
            $loginPage="user/login?continue=".$continue."&currentpage=".htmlentities(urlencode($currentPage));
            $signupPage="user/signup?continue=".$continue."&currentpage=".htmlentities(urlencode($currentPage));
            $link=anchor($signupPage, '注册')." | ".anchor($loginPage, '登录');
            return $link;
           }
           else{
            $currentPage="user/logout?continue=".$continue."&currentpage=".htmlentities(urlencode($currentPage));
            $unread_msg=$this->num_unread_msg($this->session->userdata('username'));
            $this->session->set_userdata('unread_msg', $unread_msg);          
            $link= "<font color='blue'><b>{$this->session->userdata('username')}</b></font>&nbsp;<a href='{$siteurl}/user/read_msg_theme'>$unread_msg 未读</a>&nbsp;<a href='{$siteurl}/user/my_salepost'>我的书店</a>&nbsp;&nbsp;".anchor($currentPage, '退出');
            return $link;
         }
       }
    }


     /**
     * 判断员工用户是否登录并编码网页右上方
     */
    function get_staff_status(){
        $this->load->library('session');
        $siteurl=site_url();
        $currentPage=$siteurl."/backend/logout";
        if($this->session->userdata('staffid')==""){
            $link="请登录！";
            return $link;
         }
        else{
            $link= "<font color='blue'><b>员工编号：{$this->session->userdata('staffid')}</b></font>&nbsp;&nbsp;". "<a href='$currentPage'>退出</a>";
            return $link;
         }
    }

    /**
     * 查询所有收书人id
     */
    function get_collectors(){
        $this->db->where('role', 2); //收书人的角色编号是2
        $query=$this->db->get('staff');
        if( ($query->row_array())==null ){
            return 0;   //如果数据库中没有符合此用户名的记录，则返回-1
        }
        else{
            $result=$query->result();
            return $result;
        }
    }


   /**
    * 根据用户名获取用户信息
    */
    function get_userinfo($username){
        $this->db->where('id', $username);
        $query=$this->db->get('user');
        if( ($query->row_array())==null ){
            return -1;   //如果数据库中没有符合此用户名的记录，则返回-1
        }
        else{
            $result=$query->result();
            return $result[0];
        }
    }

    /**
    * 编辑用户信息
    */
    function edit_info($data,$ischanged){
        if( $ischanged == "1" ){
            $userinfo =  array(
                'province_id' => $data['province'],
                'city_id' => $data['city'],
                'school_id' => $data['school'],
                'campus_id' => $data['campus'],
                'shopname'=>$data['shopname'],
                'email' => $data['email'],
                'name'=>$data['name'],
                'phone' => $data['phone'],
                'short_phone' => $data['short_phone'],
                'dorm' => $data['dorm'],
                'qq' => $data['qq']
                );
        }
        else{
            $userinfo =  array(
                'shopname'=>$data['shopname'],
                'email' => $data['email'],
                'name'=>$data['name'],
                'phone' => $data['phone'],
                'short_phone' => $data['short_phone'],
                'dorm' => $data['dorm'],
                'qq' => $data['qq']
                );
        }
        $this->db->where("id",$data['user_id']);
        $this->db->update('user', $userinfo);
    }

    /**
     * 修改密码  这里需要把传入数据改为字符串！
     */
    function change_password($user_id,$new_password){
        $new_pass=MD5($new_password);
        $update_string="UPDATE user SET password='{$new_pass}' WHERE id={$user_id}";
        $update=$this->db->query($update_string);
        if(($this->db->affected_rows())!=1){
            return 0;      //如果删除操作影响的行数不为1，则表示删除失败，返回0
        }
        else{
            return 1;    //如果删除成功，返回1
        }

    }


     /**
     * 为B2C功能更新用户信息
     */
    function update_profile_forb2c($client){
        $update = "update user set dorm ='" .$client->dorm."' , phone='".$client->phone."' , short_phone='".$client->short_phone."' , name='".$client->consignee."' where id ='" .$client->user_id. "'";
        $this->db->query($update);
        if(($this->db->affected_rows())!=1){
            return 0;      //如果更新操作影响的行数不为1，则表示删除失败，返回0
        }
        else{
            return 1;    //如果删除成功，返回1
        }

    }

    /**
     * 修改电子邮箱地址  这里需要把传入数据改为字符串！
     */
    function change_email($user_id,$new_email){
        $update_string="UPDATE user SET email='{$new_email}' WHERE id={$user_id}";
        $update=$this->db->query($update_string);
        if(($this->db->affected_rows())!=1){
            return 0;      //如果删除操作影响的行数不为1，则表示删除失败，返回0
        }
        else{
            return 1;    //如果删除成功，返回1
        }
    }


    /**
     * 获取所有用户信息
     */
    function get_all_users($role){
        $query_string="SELECT * FROM user WHERE role='{$role}'";
        $query = $this->db->query($query_string);
        if( ($query->num_rows())==0 ){
            return null;
        }
        else{
            $result=$query->result();
            return $result;
        }
    }

	function get_shop_car($username){
        $query_string="SELECT * FROM shop_car WHERE user_id='{$username}'";
        $query = $this->db->query($query_string);
        return $query->result();
    }

}
?>